/** 
 * File-Name:CommonDao.java
 *
 * Created on 2010-12-21 上午11:08:51
 * 
 * @author: Neo (neolimeng@gmail.com)
 * Software Engineering Institute, Peking University, China
 * 
 * Copyright (c) 2009, Peking University
 * 
 *
 */
package neoutil;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.log4j.Logger;

/**
 * Description:
 * 
 * @author: Neo (neolimeng@gmail.com) Software Engineering Institute, Peking
 *          University, China
 * @version 1.0 2010-12-21 上午11:08:51
 */
public class CommonDao<T, PK extends Serializable> {
	private static Logger log = Logger.getLogger(CommonDao.class);
	private Class<T> t;
	private Connection connection;

	public CommonDao(Class<T> t, Connection connection) {
		if (t == null || connection == null) {
			System.err.println("CommonDao中传入的参数为null！！");
			log.error("CommonDao中传入的参数为null！！");
		} else {
			this.t = t;
			this.connection = connection;
		}
	}

	/**
	 * Description: 保存对象到数据库，通用类。
	 * 
	 * @param keys
	 *            要插入数据的字段
	 * @param values
	 *            要插入的值
	 */
	public void save(Object[] keys, Object[] values) {
		StringBuffer sql = new StringBuffer();
		StringBuffer valuePart = new StringBuffer();
		sql.append("INSERT INTO " + t.getSimpleName().toLowerCase() + "(");
		valuePart.append(") VALUES(");
		if (keys.length != values.length) {
			log.error("save方法传入的Key和Value个数不相同！");
			return;
		}
		for (int i = 0; i < keys.length; i++) {
			sql.append(keys[i]);
			valuePart.append("?");
			if (i < keys.length - 1) {
				sql.append(", ");
				valuePart.append(", ");
			} else {
				sql.append(valuePart.toString() + ")");
			}
		}
		connection = DaoSupport.getConnection();
		execute(sql.toString(), values);
	}

	public T getById(Long id) {
		String sql = "SELECT * FROM " + t.getSimpleName() + " WHERE id=?";
		List<T> ts = find(sql, id);
		if (ts == null) {
			return null;
		} else if (ts.size() == 1) {
			return ts.get(0);
		} else {
			log.error("查询到的个数不唯一，无法得到唯一确定值！");
			return null;
		}
	}

	public T getByUuid(String uuid) {
		String sql = "SELECT * FROM " + t.getSimpleName() + " WHERE uuid=?";
		List<T> ts = find(sql, uuid);
		if (ts == null) {
			return null;
		} else if (ts.size() == 1) {
			return ts.get(0);
		} else {
			log.error("查询到的个数不唯一，无法得到唯一确定值！");
			return null;
		}
	}

	public List<T> getByProperty(Object key, Object value) {
		return getByProperties(new Object[] { key }, new Object[] { value });
	}

	public List<T> getByProperties(Object[] keys, Object[] values) {
		String sql = "SELECT * FROM " + t.getSimpleName().toLowerCase()
				+ " WHERE " + keys[0] + " = " + values[0];
		if (keys.length > 1) {
			for (int i = 1; i < keys.length; i++) {
				sql += " AND " + keys[i] + " = " + values[i];
			}
		}
		return find(sql);
	}

	public List<T> getAll() {
		String sql = "SELECT * FROM " + t.getSimpleName();
		return find(sql);
	}

	public Page<T> getAll(final Page<T> page) {
		String sql = "SELECT * FROM " + t.getSimpleName();
		return find(page, sql);
	}

	public void execute(String sql, Object... params) {
		Long start = System.currentTimeMillis();
		try {
			QueryRunner queryRunner = new QueryRunner();
			queryRunner.update(connection, sql, params);
		} catch (Exception e) {
			e.printStackTrace();
		}
		log.info("execute方法执行的时间为：" + (System.currentTimeMillis() - start)
				+ "ms。SQL语句为" + sql);
	}

	public List<T> find(String sql, Object... params) {
		Long start = System.currentTimeMillis();
		List<T> entities = null;
		ResultSetHandler<List<T>> handler = new BeanListHandler(t);
		QueryRunner queryRunner = new QueryRunner();

		try {
			entities = queryRunner.query(connection, sql, handler, params);

			return entities;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			if (entities != null) {
				log.info("find查询到的Entity个数为：" + entities.size() + "，执行的时间为："
						+ (System.currentTimeMillis() - start) + "ms。SQL语句为："
						+ sql);
			} else {
				log.info("find未查询到任何Entity!");
			}
		}
	}

	public Page<T> find(final Page<T> page, String sql, Object... params) {
		Long start = System.currentTimeMillis();
		ResultSetHandler<List<T>> handler = new BeanListHandler(t);
		QueryRunner queryRunner = new QueryRunner();
		int totalNum = 0;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			String countSql = "SELECT count(*) "
					+ sql.substring(sql.toLowerCase().indexOf("from"));

			preparedStatement = connection.prepareStatement(countSql);
			queryRunner.fillStatement(preparedStatement, params);
			resultSet = preparedStatement.executeQuery();
			log.info(countSql);

			while (resultSet.next()) {
				totalNum = resultSet.getInt(1);
			}
			log
					.info("中间执行的时间为：" + (System.currentTimeMillis() - start)
							+ "ms。");
			resultSet.close();
			preparedStatement.close();

			if (totalNum == 0) {
				return null;
			} else {
				page.setTotalCount(totalNum);
				int startNum = (page.getPageNo() - 1) * page.getPageSize();
				int count = page.getPageSize();
				sql += " limit " + startNum + "," + count;
				List<T> entities = queryRunner.query(connection, sql, handler,
						params);
				page.setResult(entities);

				return page;
			}

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			if (totalNum != 0) {
				log.info("find查询到的Entity个数为：" + page.getResult().size()
						+ "，执行的时间为：" + (System.currentTimeMillis() - start)
						+ "ms。SQL语句为：" + sql);
			} else {
				log.info("find未查询到任何Entity!");
			}
		}
	}
}
